﻿using BCCommon.OAEnums;
using BCEntity.OA.Notification.NotificationRecord;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
namespace BCData.OA.Notification.NotificationRecord
{
    /// <summary>
    /// OA系统通知记录
    /// </summary>
	public class NotificationRecordData : INotificationRecordData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM OA_NotificationRecord";
        private const string QUERY = @"SELECT NotificationRecordId,PageBusinessName,RelatedWorkflowCategoryId,RelatedCategoryCode,RelatedCategoryName,RelatedTaskRecordNo,
                                            RelatedBusinessCategoryId,RelatedBusinessCategoryName,RelatedBusinessRecordId,RelatedReferenceNo,RelatedBusinessTaskLevel,CompanyId,Title,CompanyName,
                                            DepartmentId,DepartmentName,AllowJobTitle,AllowJobTitleStr,ReceiverId,ReceiverRole,ReceiverName,OwnerId,OwnerRole,OwnerName,IsTransferred,Content,
                                            StartTime,EndTime,
                                            StartYear,StartMonth,ActualEndTime,Timeout,TimeoutSecond,ProcessBar,CreateTime,Status,RelatedType,Level,ProcessingTime,ProcessedTime,
                                            CloseRemark,CloseTime,CloseAdminId,CloseAdminName,NotificationRange,NotificationCategoryId,NotificationCategoryCode,NotificationCategoryName,
                                            PageBusinessConfigId,PageBusinessCode 
                                        FROM OA_NotificationRecord";

        public NotificationRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public async Task<bool> Exists(long notificationRecordId)
        {
            string sql = string.Format(@"{0} WHERE NotificationRecordId=?NotificationRecordId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NotificationRecordId", notificationRecordId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        public async Task<bool> AddForProcedure(NotificationRecordEntity entity)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("PTitle", entity.Title, DbType.AnsiString, size: 30);
            parameters.Add("PContent", entity.Content, DbType.String);
            parameters.Add("PNotificationRange", entity.NotificationRange.ToString(), DbType.AnsiString);
            parameters.Add("PNotificationCategoryCode", entity.NotificationCategoryCode, DbType.AnsiString, size: 50);
            parameters.Add("PRelatedReferenceNo", entity.RelatedReferenceNo, DbType.AnsiString, size: 50);
            parameters.Add("PStartTime", entity.StartTime, DbType.DateTime);
            parameters.Add("PEndTime", entity.EndTime, DbType.DateTime);
            return await this.databaseContext.Slave.ExecuteAsync("PROC_OA_SendNotificationRecord", parameters) > 0;
        }

        public async Task<NotificationRecordEntity> Add(NotificationRecordEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("OA_NotificationRecord", new string[] { "PageBusinessName", "RelatedWorkflowCategoryId", "RelatedCategoryCode", "RelatedCategoryName",
                "RelatedTaskRecordNo", "RelatedBusinessCategoryId", "RelatedBusinessCategoryName", "RelatedBusinessRecordId","RelatedReferenceNo" ,"RelatedBusinessTaskLevel","AllowJobTitle","AllowJobTitleStr",
                "CompanyId", "CompanyName", "DepartmentId","DepartmentName", "IsTransferred", "Title","Content","ReceiverId", "ReceiverRole", "ReceiverName", "OwnerId", "OwnerRole",
                "OwnerName", "StartTime", "EndTime", "StartYear", "StartMonth", "ProcessBar", "Status", "RelatedType", "Level", "NotificationRange" ,"PageBusinessConfigId","PageBusinessCode",
                "NotificationCategoryId","NotificationCategoryCode", "NotificationCategoryName" });
            sql = string.Format("{0} {1} WHERE NotificationRecordId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PageBusinessName", entity.PageBusinessName, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedWorkflowCategoryId", entity.RelatedWorkflowCategoryId, DbType.Int64, size: 20);
            parameters.Add("?RelatedCategoryCode", entity.RelatedCategoryCode, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedCategoryName", entity.RelatedCategoryName, DbType.AnsiString, size: 30);
            parameters.Add("?RelatedTaskRecordNo", entity.RelatedTaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedBusinessCategoryId", entity.RelatedBusinessCategoryId, DbType.Int64, size: 20);
            parameters.Add("?RelatedBusinessCategoryName", entity.RelatedBusinessCategoryName, DbType.AnsiString, size: 30);
            parameters.Add("?RelatedBusinessRecordId", entity.RelatedBusinessRecordId, DbType.Int64, size: 20);
            parameters.Add("?RelatedReferenceNo", entity.RelatedReferenceNo, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedBusinessTaskLevel", entity.RelatedBusinessTaskLevel.ToString(), DbType.AnsiString);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 11);
            parameters.Add("?Title", entity.Title, DbType.AnsiString, size: 30);
            parameters.Add("?CompanyName", entity.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentId", entity.DepartmentId, DbType.Int64, size: 20);
            parameters.Add("?DepartmentName", entity.DepartmentName, DbType.AnsiString, size: 100);
            parameters.Add("?AllowJobTitle", entity.AllowJobTitle, DbType.AnsiString, size: 1000);
            parameters.Add("?AllowJobTitleStr", entity.AllowJobTitleStr, DbType.AnsiString, size: 1000);
            parameters.Add("?ReceiverId", entity.ReceiverId, DbType.Int64, size: 20);
            parameters.Add("?ReceiverRole", entity.ReceiverRole.ToString(), DbType.AnsiString);
            parameters.Add("?ReceiverName", entity.ReceiverName, DbType.AnsiString, size: 50);
            parameters.Add("?OwnerId", entity.OwnerId, DbType.Int64, size: 20);
            parameters.Add("?OwnerRole", entity.OwnerRole.ToString(), DbType.AnsiString);
            parameters.Add("?OwnerName", entity.OwnerName, DbType.AnsiString, size: 50);
            parameters.Add("?IsTransferred", entity.IsTransferred, DbType.Int64, size: 20);
            parameters.Add("?StartTime", entity.StartTime, DbType.DateTime);
            parameters.Add("?EndTime", entity.EndTime, DbType.DateTime);
            parameters.Add("?StartYear", entity.StartYear, DbType.Int32, size: 11);
            parameters.Add("?StartMonth", entity.StartMonth, DbType.Int32, size: 11);
            parameters.Add("?ProcessBar", entity.ProcessBar, DbType.Double);
            parameters.Add("?Status", entity.Status.ToString(), DbType.AnsiString);
            parameters.Add("?RelatedType", entity.RelatedType.ToString(), DbType.AnsiString);
            parameters.Add("?Level", entity.Level.ToString(), DbType.AnsiString);
            parameters.Add("?NotificationRange", entity.NotificationRange.ToString(), DbType.AnsiString);
            parameters.Add("?PageBusinessConfigId", entity.PageBusinessConfigId, DbType.Int64, size: 20);
            parameters.Add("?PageBusinessCode", entity.PageBusinessCode, DbType.AnsiString, size: 150);

            parameters.Add("?NotificationCategoryId", entity.NotificationCategoryId, DbType.Int64, size: 20);
            parameters.Add("?NotificationCategoryCode", entity.NotificationCategoryCode, DbType.AnsiString, size: 50);
            parameters.Add("?NotificationCategoryName", entity.NotificationCategoryName, DbType.AnsiString, size: 30);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<NotificationRecordEntity>(sql, parameters, databaseContext.Transaction);

        }

        public async Task<bool> Add(List<NotificationRecordEntity> entities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = SqlCommands.GetInsertSql("OA_NotificationRecord", new string[] { "PageBusinessName", "RelatedWorkflowCategoryId", "RelatedCategoryCode", "RelatedCategoryName",
                "RelatedTaskRecordNo", "RelatedBusinessCategoryId", "RelatedBusinessCategoryName", "RelatedBusinessRecordId", "RelatedReferenceNo" ,"RelatedBusinessTaskLevel","AllowJobTitle","AllowJobTitleStr",
                "CompanyId", "CompanyName", "DepartmentId","DepartmentName", "IsTransferred", "Title","Content","ReceiverId", "ReceiverRole", "ReceiverName", "OwnerId", "OwnerRole",
                "OwnerName", "StartTime", "EndTime", "StartYear", "StartMonth", "ProcessBar", "Status", "RelatedType", "Level", "NotificationRange" ,"PageBusinessConfigId","PageBusinessCode",
                "NotificationCategoryId", "NotificationCategoryCode", "NotificationCategoryName" });
            foreach (var entity in entities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?PageBusinessName", entity.PageBusinessName, DbType.AnsiString, size: 50);
                parameters.Add("?RelatedWorkflowCategoryId", entity.RelatedWorkflowCategoryId, DbType.Int64, size: 20);
                parameters.Add("?RelatedCategoryCode", entity.RelatedCategoryCode, DbType.AnsiString, size: 50);
                parameters.Add("?RelatedCategoryName", entity.RelatedCategoryName, DbType.AnsiString, size: 30);
                parameters.Add("?RelatedTaskRecordNo", entity.RelatedTaskRecordNo, DbType.AnsiString, size: 50);
                parameters.Add("?RelatedBusinessCategoryId", entity.RelatedBusinessCategoryId, DbType.Int64, size: 20);
                parameters.Add("?RelatedBusinessCategoryName", entity.RelatedBusinessCategoryName, DbType.AnsiString, size: 30);
                parameters.Add("?RelatedBusinessRecordId", entity.RelatedBusinessRecordId, DbType.Int64, size: 20);
                parameters.Add("?RelatedReferenceNo", entity.RelatedReferenceNo, DbType.AnsiString, size: 50);
                parameters.Add("?RelatedBusinessTaskLevel", entity.RelatedBusinessTaskLevel.ToString(), DbType.AnsiString);
                parameters.Add("?Content", entity.Content, DbType.String);
                parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 11);
                parameters.Add("?Title", entity.Title, DbType.AnsiString, size: 30);
                parameters.Add("?CompanyName", entity.CompanyName, DbType.AnsiString, size: 50);
                parameters.Add("?DepartmentId", entity.DepartmentId, DbType.Int64, size: 20);
                parameters.Add("?DepartmentName", entity.DepartmentName, DbType.AnsiString, size: 100);
                parameters.Add("?AllowJobTitle", entity.AllowJobTitle, DbType.AnsiString, size: 1000);
                parameters.Add("?AllowJobTitleStr", entity.AllowJobTitleStr, DbType.AnsiString, size: 1000);
                parameters.Add("?ReceiverId", entity.ReceiverId, DbType.Int64, size: 20);
                parameters.Add("?ReceiverRole", entity.ReceiverRole?.ToString(), DbType.AnsiString);
                parameters.Add("?ReceiverName", entity.ReceiverName, DbType.AnsiString, size: 50);
                parameters.Add("?OwnerId", entity.OwnerId, DbType.Int64, size: 20);
                parameters.Add("?OwnerRole", entity.OwnerRole?.ToString(), DbType.AnsiString);
                parameters.Add("?OwnerName", entity.OwnerName, DbType.AnsiString, size: 50);
                parameters.Add("?IsTransferred", entity.IsTransferred, DbType.Int64, size: 20);
                parameters.Add("?StartTime", entity.StartTime, DbType.DateTime);
                parameters.Add("?EndTime", entity.EndTime, DbType.DateTime);
                parameters.Add("?StartYear", entity.StartYear, DbType.Int32, size: 11);
                parameters.Add("?StartMonth", entity.StartMonth, DbType.Int32, size: 11);
                parameters.Add("?ProcessBar", entity.ProcessBar, DbType.Double);
                parameters.Add("?Status", entity.Status.ToString(), DbType.AnsiString);
                parameters.Add("?RelatedType", entity.RelatedType.ToString(), DbType.AnsiString);
                parameters.Add("?Level", entity.Level.ToString(), DbType.AnsiString);
                parameters.Add("?NotificationRange", entity.NotificationRange.ToString(), DbType.AnsiString);
                parameters.Add("?PageBusinessConfigId", entity.PageBusinessConfigId, DbType.Int64, size: 20);
                parameters.Add("?PageBusinessCode", entity.PageBusinessCode, DbType.AnsiString, size: 150);

                parameters.Add("?NotificationCategoryId", entity.NotificationCategoryId, DbType.Int64, size: 20);
                parameters.Add("?NotificationCategoryCode", entity.NotificationCategoryCode, DbType.AnsiString, size: 50);
                parameters.Add("?NotificationCategoryName", entity.NotificationCategoryName, DbType.AnsiString, size: 30);

                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return await this.databaseContext.Master.TransactionExecuteAsync(sqls, dynamicParameters, databaseContext.Transaction) > 0;

        }

        public async Task<NotificationRecordEntity> Update(NotificationRecordEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("OA_NotificationRecord", new string[] { "PageBusinessName", "RelatedWorkflowCategoryId", "RelatedCategoryCode", "RelatedCategoryName",
                "RelatedTaskRecordNo", "RelatedBusinessCategoryId", "RelatedBusinessCategoryName", "RelatedBusinessRecordId", "RelatedReferenceNo" ,"RelatedBusinessTaskLevel", "CompanyId", "Title",
                "CompanyName", "DepartmentId", "DepartmentName","AllowJobTitle","AllowJobTitleStr", "ReceiverId", "ReceiverRole", "ReceiverName", "OwnerId", "OwnerRole", "OwnerName", "IsTransferred", "Content",
                "StartTime", "EndTime", "StartYear", "StartMonth", "ActualEndTime", "Timeout", "TimeoutSecond", "ProcessBar", "Status", "RelatedType", "Level", "ProcessingTime",
                "ProcessedTime", "CloseRemark", "CloseTime", "CloseAdminId","CloseAdminName", "NotificationRange", "NotificationCategoryId", "NotificationCategoryName", "PageBusinessConfigId","PageBusinessCode" },
                new string[] { "NotificationRecordId" });
            sql = string.Format("{0} {1} WHERE NotificationRecordId=?NotificationRecordId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NotificationRecordId", entity.NotificationRecordId, DbType.Int64, size: 20);
            parameters.Add("?PageBusinessName", entity.PageBusinessName, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedWorkflowCategoryId", entity.RelatedWorkflowCategoryId, DbType.Int64, size: 20);
            parameters.Add("?RelatedCategoryCode", entity.RelatedCategoryCode, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedCategoryName", entity.RelatedCategoryName, DbType.AnsiString, size: 30);
            parameters.Add("?RelatedTaskRecordNo", entity.RelatedTaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedBusinessCategoryId", entity.RelatedBusinessCategoryId, DbType.Int64, size: 20);
            parameters.Add("?RelatedBusinessCategoryName", entity.RelatedBusinessCategoryName, DbType.AnsiString, size: 30);
            parameters.Add("?RelatedBusinessRecordId", entity.RelatedBusinessRecordId, DbType.Int64, size: 20);
            parameters.Add("?RelatedReferenceNo", entity.RelatedReferenceNo, DbType.AnsiString, size: 50);
            parameters.Add("?RelatedBusinessTaskLevel", entity.RelatedBusinessTaskLevel.ToString(), DbType.AnsiString);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 11);
            parameters.Add("?Title", entity.Title, DbType.AnsiString, size: 30);
            parameters.Add("?CompanyName", entity.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentId", entity.DepartmentId, DbType.Int64, size: 20);
            parameters.Add("?DepartmentName", entity.DepartmentName, DbType.AnsiString, size: 100);
            parameters.Add("?AllowJobTitle", entity.AllowJobTitle, DbType.AnsiString, size: 1000);
            parameters.Add("?AllowJobTitleStr", entity.AllowJobTitleStr, DbType.AnsiString, size: 1000);
            parameters.Add("?ReceiverId", entity.ReceiverId, DbType.Int64, size: 20);
            parameters.Add("?ReceiverRole", entity.ReceiverRole?.ToString(), DbType.AnsiString);
            parameters.Add("?ReceiverName", entity.ReceiverName, DbType.AnsiString, size: 50);
            parameters.Add("?OwnerId", entity.OwnerId, DbType.Int64, size: 20);
            parameters.Add("?OwnerRole", entity.OwnerRole?.ToString(), DbType.AnsiString);
            parameters.Add("?OwnerName", entity.OwnerName, DbType.AnsiString, size: 50);
            parameters.Add("?IsTransferred", entity.IsTransferred, DbType.Int64, size: 20);
            parameters.Add("?Content", entity.Content, DbType.String);
            parameters.Add("?StartTime", entity.StartTime, DbType.DateTime);
            parameters.Add("?EndTime", entity.EndTime, DbType.DateTime);
            parameters.Add("?StartYear", entity.StartYear, DbType.Int32, size: 11);
            parameters.Add("?StartMonth", entity.StartMonth, DbType.Int32, size: 11);
            parameters.Add("?ActualEndTime", entity.ActualEndTime, DbType.DateTime);
            parameters.Add("?Timeout", entity.Timeout, DbType.DateTime);
            parameters.Add("?TimeoutSecond", entity.TimeoutSecond, DbType.Int32, size: 11);
            parameters.Add("?ProcessBar", entity.ProcessBar, DbType.Double);
            parameters.Add("?Status", entity.Status.ToString(), DbType.AnsiString);
            parameters.Add("?RelatedType", entity.RelatedType.ToString(), DbType.AnsiString);
            parameters.Add("?Level", entity.Level.ToString(), DbType.AnsiString);
            parameters.Add("?ProcessingTime", entity.ProcessingTime, DbType.DateTime);
            parameters.Add("?ProcessedTime", entity.ProcessedTime, DbType.DateTime);
            parameters.Add("?CloseRemark", entity.CloseRemark, DbType.String);
            parameters.Add("?CloseTime", entity.CloseTime, DbType.DateTime);
            parameters.Add("?CloseAdminId", entity.CloseAdminId, DbType.Int64, size: 20);
            parameters.Add("?CloseAdminName", entity.CloseAdminName, DbType.AnsiString, size: 20);
            parameters.Add("?NotificationRange", entity.NotificationRange.ToString(), DbType.AnsiString);
            parameters.Add("?NotificationCategoryId", entity.NotificationCategoryId, DbType.Int64, size: 20);
            parameters.Add("?NotificationCategoryName", entity.NotificationCategoryName, DbType.AnsiString, size: 30);
            parameters.Add("?PageBusinessConfigId", entity.PageBusinessConfigId, DbType.Int64, size: 20);
            parameters.Add("?PageBusinessCode", entity.PageBusinessCode, DbType.AnsiString, size: 150);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<NotificationRecordEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<bool> Delete(long notificationRecordId)
        {
            string sql = @"DELETE FROM OA_NotificationRecord WHERE NotificationRecordId=?NotificationRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NotificationRecordId", notificationRecordId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<NotificationRecordEntity> GetEntity(long notificationRecordId)
        {
            string sql = string.Format(@"{0} WHERE NotificationRecordId=?NotificationRecordId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NotificationRecordId", notificationRecordId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<NotificationRecordEntity>(sql, parameters);
        }

        public async Task<NotificationRecordEntity> GetEntity(string notificationCategoryCode, string relatedReferenceNo)
        {
            string sql = string.Format(@"{0} WHERE NotificationCategoryCode=?NotificationCategoryCode AND RelatedReferenceNo=?RelatedReferenceNo", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NotificationCategoryCode", notificationCategoryCode, DbType.AnsiString);
            parameters.Add("?RelatedReferenceNo", relatedReferenceNo, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<NotificationRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<NotificationRecordEntity>> GetList()
        {
            return await this.databaseContext.Slave.QueryAsync<NotificationRecordEntity>(QUERY);
        }

        public async Task<(IEnumerable<NotificationRecordEntity>, int)> Query(OARelatedType relatedType, OAProcessStatus status, DateTime? start, DateTime? end, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("RelatedType=?RelatedType");
            parameters.Add("?RelatedType", relatedType.ToString(), DbType.AnsiString);
            wheres.Add("Status=?Status");
            parameters.Add("?Status", status.ToString(), DbType.AnsiString);
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM OA_NotificationRecord {1}",
                   QUERY,
                   wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                   (pageIndex - 1) * pageSize,
                   pageSize);
            return await this.databaseContext.Slave.QueryPageableListAsync<NotificationRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<NotificationRecordSumViewEntity>> GetNotificationList(OARelatedType relatedType, OAProcessStatus status, long companyId, long departmentId, string jobTitle, long adminId)
        {
            string sql = string.Format(@"SELECT *,
                                            COUNT(NotificationCategoryCode) AS NotificationCategoryCount, 
                                            GROUP_CONCAT(NotificationRecordId SEPARATOR ',') AS NotificationRecordIds,
                                            GROUP_CONCAT(RelatedBusinessRecordId SEPARATOR ',') AS RelatedBusinessRecordIds,
                                            GROUP_CONCAT(RelatedReferenceNo SEPARATOR ',') AS RelatedReferenceNos
                                        FROM (
                                            {0} WHERE Status=?Status AND RelatedType=?RelatedType AND CompanyId=?CompanyId AND DepartmentId=?DepartmentId AND NotificationRange='Department'
                                            UNION 
                                            {0} WHERE Status=?Status AND RelatedType=?RelatedType AND CompanyId=?CompanyId AND DepartmentId=?DepartmentId AND NotificationRange='JobTitle' AND FIND_IN_SET(?JobTitle,AllowJobTitle) 
                                            UNION 
                                            {0} WHERE Status=?Status AND RelatedType=?RelatedType AND CompanyId=?CompanyId AND DepartmentId=?DepartmentId AND NotificationRange='Staff' AND ReceiverRole='Admin' AND ReceiverId=?AdminId
                                        ) T GROUP BY NotificationCategoryCode"
                            , QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?RelatedType", relatedType.ToString(), DbType.AnsiString);
            parameters.Add("?CompanyId", companyId, DbType.Int64, size: 20);
            parameters.Add("?DepartmentId", departmentId, DbType.Int64, size: 20);
            parameters.Add("?JobTitle", jobTitle, DbType.AnsiString);
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?Status", status.ToString(), DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<NotificationRecordSumViewEntity>(sql, parameters);
        }

    }
}
